#!/usr/bin/env python
# -*- coding: utf-8 -*-
# author : wzp
# date : modify by wzp 20220513


import pymysql

host = "127.0.0.1"
port = 3306
user = "root"
passwd = "qwe123"
db = "message"
conn = None

###########create table
#create table sms_info(
#	sender int,
#	sender_name varchar(40),
#	content varchar(160),
#	insert_time timestamp not null default CURRENT_TIMESTAMP
#)

def None2NULL(v):
    if v is None:
        return "NULL"
    else:
        return v

def perform(sql):
    global conn, cur
    try:
        cur.execute("select 1")
        cur.fetchone()
    except:
        conn = pymysql.Connect(host=host,port=port,user=user,passwd=passwd,db=db,charset="utf8")
        cur = conn.cursor()
        print("build a new connection")

    try:
        cur.execute(sql)
        conn.commit()
    except Exception as e:
        print("sql执行失败：{}".format(e.args))
        print(sql)


def performQuery(sql):
    global conn, cur
    try:
        cur.execute("select 1")
        cur.fetchone()
    except:
        conn = pymysql.Connect(host=host, port=port, user=user, passwd=passwd, db=db, charset="utf8")
        cur = conn.cursor()
        print("build a new connection")

    try:
        cur.execute(sql)
        return cur.fetchall()
    except Exception as e:
        print("sql执行失败：{}".format(e.args))
        print(sql)



def toMYSQL(sender_name,content):
    sender_name = None2NULL(sender_name)
    content = None2NULL(content)

    sql = """insert into sms_info(sender_name,content) values ('{0}','{1}');""".format(sender_name,content)
    print(sql)
    perform(sql)

def QueryMySQL(content):
    sql = """select content,insert_time from sms_info where content like '%{}%' order by insert_time desc limit 5;
    """.format(content)
    print(sql)
    return performQuery(sql)

if __name__ == "__main__":
    # print(int(time.time()))
    r = QueryMySQL("123")
    for line in r:
        print(line)


